package healthcheck

const (
	// application mysql
	applicationMySQLVariables = `
		SELECT variable_name, variable_value
		FROM %s.global_variables
		WHERE variable_name IN (%s);
    `
	applicationMySQLTableSize = `
    	SELECT table_schema AS db_name,
			   table_name AS table_name,
			   table_rows AS table_rows,
			   TRUNCATE((data_length + index_length) / 1024 / 1024 / 1024, 2) AS table_size
		FROM information_schema.tables
		WHERE table_type = 'BASE TABLE'
		  AND table_rows > ?
		ORDER BY table_rows DESC;
    `
	// Prometheus API
	PrometheusAvgBackupFailedRatioV1 = `
		1-(sum(mysqldumpbackup{instance=~"%s",type="status"})/count(mysqldumpbackup{instance=~"%s",type="status"})+
		sum(xtrabackup{instance=~"%s",type="status"})/count(xtrabackup{instance=~"%s",type="status"})+
		sum(mysqldumpnbubackup{instance=~"%s"})/count(mysqldumpnbubackup{instance=~"%s"})+
		sum(nbubackup{instance=~"%s"})/count(nbubackup{instance=~"%s"}))/4
    `
	PrometheusAvgBackupFailedRatioV2 = `
		1-(sum(mysqldumpbackup{node_name=~"%s",type="status"})/count(mysqldumpbackup{node_name=~"%s",type="status"})+
		sum(xtrabackup{node_name=~"%s",type="status"})/count(xtrabackup{node_name=~"%s",type="status"})+
		sum(mysqldumpnbubackup{node_name=~"%s"})/count(mysqldumpnbubackup{node_name=~"%s"})+
		sum(nbubackup{node_name=~"%s"})/count(nbubackup{node_name=~"%s"}))/4
    `
	PrometheusStatisticsFailedRatioV1 = `
		1-(sum(mysqlupdbstat{instance=~"%s",type="dbupstatus"})/count(mysqlupdbstat{instance=~"%s",type="dbupstatus"}))
	`
	PrometheusStatisticsFailedRatioV2 = `
		1-(sum(mysqlupdbstat{node_name=~"%s",type="dbupstatus"})/count(mysqlupdbstat{node_name=~"%s",type="dbupstatus"}))
	`
	PrometheusCPUUsageV1 = `
		clamp_max(sum by () ((avg by (mode) (
		(clamp_max(rate(node_cpu{instance=~"%s",mode!="idle",mode!="iowait"}[5m]),1)) or
		(clamp_max(irate(node_cpu{instance=~"%s",mode!="idle",mode!="iowait"}[5m]),1)) )) or
		sum by () (
		avg_over_time(node_cpu_average{instance=~"%s",mode!="total",mode!="idle"}[5m]) or
		avg_over_time(node_cpu_average{instance=~"%s",mode!="total",mode!="idle"}[5m])) unless
		(avg_over_time(node_cpu_average{instance=~"%s",mode="total",job="rds-basic"}[5m]) or
		avg_over_time(node_cpu_average{instance=~"%s",mode="total",job="rds-basic"}[5m]))
		),100)
    `
	PrometheusCPUUsageV2 = `
		clamp_max(sum by () ((avg by (mode) ( 
		(clamp_max(rate(node_cpu_seconds_total{node_name=~"%s",mode!="idle",mode!="iowait"}[5m]),1)) or 
		(clamp_max(irate(node_cpu_seconds_total{node_name=~"%s",mode!="idle",mode!="iowait"}[5m]),1)) )) or
		sum by () (
		avg_over_time(node_cpu_average{node_name=~"%s",mode!="total",mode!="idle"}[5m]) or 
		avg_over_time(node_cpu_average{node_name=~"%s",mode!="total",mode!="idle"}[5m])) unless
		(avg_over_time(node_cpu_average{node_name=~"%s",mode="total",job="rds-basic"}[5m]) or 
		avg_over_time(node_cpu_average{node_name=~"%s",mode="total",job="rds-basic"}[5m]))
		),100)
    `
	PrometheusFileSystemV1 = `
		node_filesystem_files{instance=~"%s",fstype!~"rootfs|selinuxfs|autofs|rpc_pipefs|tmpfs"}
    `
	PrometheusFileSystemV2 = `
		node_filesystem_files{node_name=~"%s",fstype!~"rootfs|selinuxfs|autofs|rpc_pipefs|tmpfs"}
    `
	PrometheusIOUtilV1 = `
		max by (instance) (rate(node_disk_io_time_ms{instance=~"%s"}[20s])/1000 or
		irate(node_disk_io_time_ms{instance=~"%s"}[5m])/1000)
    `
	PrometheusIOUtilV2 = `
		max by (node_name) (rate(node_disk_io_time_seconds_total{node_name=~"%s"}[20s]) or
		irate(node_disk_io_time_seconds_total{node_name=~"%s"}[5m]) or
		(max_over_time(rdsosmetrics_diskIO_util{node_name=~"%s"}[20s]) or
		max_over_time(rdsosmetrics_diskIO_util{node_name=~"%s"}[5m]))/100)
    `
	PrometheusDiskCapacityV1 = `
		1 - node_filesystem_free{instance=~"%s", mountpoint=~"(%s)", fstype!~"rootfs|selinuxfs|autofs|rpc_pipefs|tmpfs"} /
		node_filesystem_size{instance=~"%s", mountpoint=~"(%s)", fstype!~"rootfs|selinuxfs|autofs|rpc_pipefs|tmpfs"}
    `
	PrometheusDiskCapacityV2 = `
		avg by (node_name, mountpoint) (1 - (max_over_time(node_filesystem_free_bytes{node_name=~"%s", mountpoint=~"(%s)", fstype!~"rootfs|selinuxfs|autofs|rpc_pipefs|tmpfs"}[20s]) or
		max_over_time(node_filesystem_free_bytes{node_name=~"%s", mountpoint=~"(%s)", fstype!~"rootfs|selinuxfs|autofs|rpc_pipefs|tmpfs"}[5m])) /
		(max_over_time(node_filesystem_size_bytes{node_name=~"%s", mountpoint=~"(%s)", fstype!~"rootfs|selinuxfs|autofs|rpc_pipefs|tmpfs"}[20s]) or
		max_over_time(node_filesystem_size_bytes{node_name=~"%s", mountpoint=~"(%s)", fstype!~"rootfs|selinuxfs|autofs|rpc_pipefs|tmpfs"}[5m])))
   `
	PrometheusConnectionUsageV1 = `
		(max(max_over_time(mysql_global_status_threads_connected{instance=~"%s"}[20s]) or
		max_over_time(mysql_global_status_threads_connected{instance=~"%s"}[5m]))) /
		(max(max_over_time(mysql_global_variables_max_connections{instance=~"%s"}[20s]) or
		max_over_time(mysql_global_variables_max_connections{instance=~"%s"}[5m])))
    `
	PrometheusConnectionUsageV2 = `
		(max(max_over_time(mysql_global_status_threads_connected{service_name=~"%s"}[20s]) or
		max_over_time(mysql_global_status_threads_connected{service_name=~"%s"}[5m]))) /
		max((max_over_time(mysql_global_variables_max_connections{service_name=~"%s"}[20s]) or
		max_over_time(mysql_global_variables_max_connections{service_name=~"%s"}[5m])))
    `
	PrometheusAverageActiveSessionPercentsV1 = `
		avg by (instance) (avg_over_time(mysql_global_status_threads_running{instance=~"%s"}[20s]) or
		avg_over_time(mysql_global_status_threads_running{instance=~"%s"}[5m]))/
		avg by (instance) (max_over_time(mysql_global_status_threads_connected{instance=~"%s"}[20s]) or
		max_over_time(mysql_global_status_threads_connected{instance=~"%s"}[5m]))
    `
	PrometheusAverageActiveSessionPercentsV2 = `
		avg by (service_name) (avg_over_time(mysql_global_status_threads_running{service_name=~"%s"}[20s]) or
		avg_over_time(mysql_global_status_threads_running{service_name=~"%s"}[5m]))/
		avg by (service_name) (max_over_time(mysql_global_status_threads_connected{service_name=~"%s"}[20s]) or
		max_over_time(mysql_global_status_threads_connected{service_name=~"%s"}[5m]))
    `
	PrometheusCacheMissRatioV1 = `
		avg by (instance) ((rate(mysql_global_status_innodb_buffer_pool_reads{instance=~"%s"}[5m]) or
		irate(mysql_global_status_innodb_buffer_pool_reads{instance=~"%s"}[5m])) /
		(rate(mysql_global_status_innodb_buffer_pool_read_requests{instance=~"%s"}[5m]) or
		irate(mysql_global_status_innodb_buffer_pool_read_requests{instance=~"%s"}[5m])))
    `
	PrometheusCacheMissRatioV2 = `
		avg by (service_name) ((rate(mysql_global_status_innodb_buffer_pool_reads{service_name=~"%s"}[5m]) or
		irate(mysql_global_status_innodb_buffer_pool_reads{service_name=~"%s"}[5m])) /
		(rate(mysql_global_status_innodb_buffer_pool_read_requests{service_name=~"%s"}[5m]) or
		irate(mysql_global_status_innodb_buffer_pool_read_requests{service_name=~"%s"}[5m])))
    `
	// TODO: for now, PrometheusReplicationLagV1 uses the sql as same as PrometheusReplicationLagV2, may need to change someday
	PrometheusReplicationLagV1 = `
		clamp_min(avg by (service_name) ((max_over_time(mysql_slave_status_seconds_behind_master{service_name=~"%s"}[20s]) or
		max_over_time(mysql_slave_status_seconds_behind_master{service_name=~"%s"}[5m])) - (max_over_time(mysql_slave_status_sql_delay{service_name=~"%s"}[20s]) or
		max_over_time(mysql_slave_status_sql_delay{service_name=~"%s"}[20s]))),0)
	`
	PrometheusReplicationLagV2 = `
		clamp_min(avg by (service_name) ((max_over_time(mysql_slave_status_seconds_behind_master{service_name=~"%s"}[20s]) or
		max_over_time(mysql_slave_status_seconds_behind_master{service_name=~"%s"}[5m])) - (max_over_time(mysql_slave_status_sql_delay{service_name=~"%s"}[20s]) or
		max_over_time(mysql_slave_status_sql_delay{service_name=~"%s"}[20s]))),0)
	`
	// query
	MonitorMySQLQuery = `
		SELECT qc.checksum AS sql_id,
			   qc.fingerprint,
			   qe.query    AS example,
			   qe.db       AS db_name,
			   m.exec_count,
			   m.total_exec_time,
			   m.avg_exec_time,
			   m.rows_examined_max
		FROM (
				 SELECT qcm.query_class_id,
						SUM(qcm.query_count)                                        AS exec_count,
						TRUNCATE(SUM(qcm.query_time_sum), 2)                        AS total_exec_time,
						TRUNCATE(SUM(qcm.query_time_sum) / SUM(qcm.query_count), 2) AS avg_exec_time,
						qcm.rows_examined_max
				 FROM query_class_metrics qcm
						  INNER JOIN instances i ON qcm.instance_id = i.instance_id
				 WHERE i.name = ?
				   AND qcm.start_ts >= ?
				   AND qcm.start_ts < ?
				   AND qcm.rows_examined_max >= ?
				   AND qcm.query_time_sum / qcm.query_count >= ?
				 GROUP BY query_class_id
				 ORDER BY avg_exec_time DESC
				 LIMIT ?) m
				 INNER JOIN query_examples qe ON m.query_class_id = qe.query_class_id
				 INNER JOIN query_classes qc ON m.query_class_id = qc.query_class_id;
    `
	MonitorClickhouseQuery = `
		SELECT sm.sql_id,
			   m.fingerprint,
			   m.example,
			   m.db_name,
			   sm.exec_count,
			   sm.total_exec_time,
			   sm.avg_exec_time,
			   sm.rows_examined_max
		FROM (
			SELECT queryid                                               AS sql_id,
				   SUM(num_queries)                                      AS exec_count,
				   TRUNCATE(SUM(m_query_time_sum), 2)                    AS total_exec_time,
				   TRUNCATE(SUM(m_query_time_sum) / SUM(num_queries), 2) AS avg_exec_time,
				   IF(MAX(m_rows_examined_max) = 0 AND SUM(m_rows_examined_cnt) != 0,
					  SUM(m_rows_examined_sum) / SUM(m_rows_examined_cnt),
					  MAX(m_rows_examined_max))                          AS rows_examined_max
			FROM metrics
			WHERE service_type = 'mysql'
			  AND service_name = ?
			  AND period_start >= ?
			  AND period_start < ?
			  AND m_rows_examined_max >= ?
			  AND IF(m_query_time_max is null or m_query_time_max = 0,
					 m_query_time_sum / m_query_time_cnt,
					 m_query_time_max) >= ?
			GROUP BY queryid
			ORDER BY avg_exec_time DESC
			LIMIT ?) sm
			LEFT JOIN (SELECT queryid                        AS sql_id,
							  MAX(fingerprint)               AS fingerprint,
							  MAX(example)                   AS example,
							  IF(MAX(database) is null or MAX(database) = '',
								 MAX(schema), MAX(database)) AS db_name
					   FROM metrics
					   WHERE service_type = 'mysql'
						 AND service_name = ?
						 AND period_start >= ?
						 AND period_start < ?
						 AND m_rows_examined_max >= ?
						 AND IF(m_query_time_max is null or m_query_time_max = 0,
								m_query_time_sum / m_query_time_cnt,
								m_query_time_max) >= ?
					   GROUP BY queryid) m
					  ON sm.sql_id = m.sql_id;
    `
)
